﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BEELab.Data
{
    public static class DBHelper
    {
        //Connection string to BEELAB's DB
        private static readonly string connectionString = BEELab.Properties.Settings.Default.ConnectionString;

        /// <summary>
        /// Function that expects to run a command, that affects the state of the specified DB.
        /// Normally  command will be INSERT, UPDATE, DELETE, CREATE, and SET statements.
        /// </summary>
        /// <param name="command">sql command to execute on DB as string</param>
        /// <returns>The number of rows affected by the command execution</returns>
        public static int ExecuteNonQuery(SqlCommand command)
        {
            int res = 0;
            SqlConnection connection = null;
            try
            {
                //open connection to DB
                connection = new SqlConnection(connectionString);
                connection.Open();
                command.Connection = connection;
                res = command.ExecuteNonQuery();
                return res;
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                //close connection to DB
                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                    connection.Close();
            }
        }

        /// <summary>
        /// Function that expects to run a command on DB, that will return object as DataTable.
        /// </summary>
        /// <param name="command">sql command to execute on DB as string</param>
        /// <returns>requested information as DataTable object</returns>
        public static DataTable ExecuteQuery(SqlCommand command)
        {
            SqlConnection connection = null;
            try
            {
                //open connection to DB
                connection = new SqlConnection(connectionString);
                connection.Open();
                command.Connection = connection;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                dataAdapter.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                //close connection to DB
                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                    connection.Close();
            }
        }

        /// <summary>
        /// Function that expects to run a query to the DB.
        /// Normally  command will be SELECT statement.
        /// </summary>
        /// <param name="command">sql command to execute on DB as string</param>
        /// <returns>The number of rows affected by the command execution</returns>
        public static string ExecuteScalar(SqlCommand command)
        {
            SqlConnection connection = null;
            try
            {
                //open connection to DB
                connection = new SqlConnection(connectionString);
                connection.Open();
                command.Connection = connection;
                object res = command.ExecuteScalar();

                if (res == null)
                    return null;

                return res.ToString();
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                //close connection to DB
                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                    connection.Close();
            }
        }
    }
}